package com.cfs.daoimpl.sys;

import java.io.File;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

//import net.sf.ehcache.Cache;


import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.util.DateUtil;
import com.util.ExcelUtil;
import com.util.InUtil;
import com.util.LocalHostIp;
import com.cfs.po.Exp;
import com.cfs.po.sys.GenRes;
import com.cfs.po.sys.ImpResult;
import com.cfs.po.sys.Log;
import com.cfs.po.sys.LoginInfo;
import com.cfs.po.sys.LoginInfoCondition;
import com.cfs.po.sys.LoginInfoWrapper;
import com.cfs.po.sys.LoginOutWrapper;
import com.cfs.po.sys.Org;
import com.cfs.po.sys.QueryCondition;
import com.cfs.po.sys.Role;
import com.cfs.po.sys.User;
import com.cfs.po.sys.UserCondition;
import com.cfs.po.sys.UserRole;
import com.cfs.po.sys.UserWrapper;
import com.cfs.vo.Pager;

/**
 * 用户管理dao
 * 
 * @author SDZJ
 * 
 */
public class UserDao extends Dao {

	private RoleDao roleDao;
	private OrgDao orgDao;
	private DictDao dictDao;


	public RoleDao getRoleDao() {
		return roleDao;
	}

	public void setRoleDao(RoleDao roleDao) {
		this.roleDao = roleDao;
	}

	public OrgDao getOrgDao() {
		return orgDao;
	}

	public DictDao getDictDao() {
		return dictDao;
	}

	public void setDictDao(DictDao dictDao) {
		this.dictDao = dictDao;
	}

	public void setOrgDao(OrgDao orgDao) {
		this.orgDao = orgDao;
	}

	private RowMapper<User> rowMapper = new RowMapper<User>() {

		@Override
		public User mapRow(ResultSet rs, int arg1) throws SQLException {
			User user = new User();
			user.setId(rs.getLong("id"));
			user.setName(rs.getString("name"));
			user.setPass(rs.getString("pass"));
			user.setOrg(rs.getLong("org"));
			user.setRealname(rs.getString("realname"));
			user.setPhoneno(rs.getString("phoneno"));
			user.setEmail(rs.getString("email"));
//			user.setMobile(rs.getLong("MOBILE"));
//			user.setDeleted(rs.getInt("deleted"));
//			user.setDeStandType(rs.getInt("DESTANDTYPE"));
//			user.setBrand(rs.getInt("DEBRAND"));
//			user.setCategory(rs.getInt("DECATEGORY"));
//			user.setOtCategory(rs.getString("OTCATEGORY"));
			return user;
		}

	};

	private RowMapper<LoginInfo> loginOutMapper = new RowMapper<LoginInfo>() {

		@Override
		public LoginInfo mapRow(ResultSet rs, int arg1) throws SQLException {
			LoginInfo loginInfo = new LoginInfo();
			loginInfo.setUserid(rs.getLong("USERID"));
			loginInfo.setLogintime(rs.getDate("LOGINTIME"));
			loginInfo.setLogintime(rs.getDate("LOGINOUTTIME"));

			return loginInfo;
		}
	};

	public boolean checkUser(String name, String pass) {

		if (name != null && pass != null && !name.equals("") && !pass.equals("")) {
			boolean exist = false;
			long mobile = 0;
			try {
				mobile = Long.parseLong(name);
			} catch (Exception e) {
			}
			int list = jdbcTemplate.queryForInt("SELECT count(1) FROM TB_USER WHERE PASS=? AND (NAME=? OR MOBILE=?) and deleted=1", pass, name, mobile);
			if (list == 1)
				exist = true;

			return exist;
		} else
			return false;
	}

	/**
	 * 用户登录
	 * 
	 * @param name
	 * @param pass
	 * @return
	 */
	public User getcheckPass(String name, String pass) {
		// System.out.println("UserDao.checkPass: " + name + " - " + pass);

		User u = null;
		if (name != null && pass != null && !name.equals("") && !pass.equals("")) {
			
			

			long mobile = 0;
			try {
				mobile = Long.parseLong(name);
			} catch (Exception e) {
			}
			List<User> list = jdbcTemplate.query(DB_SQL.USER_QUERY_BY_NAME_PASS, rowMapper, pass, name, mobile);
			if (list != null && list.size() == 1) {
				//验证组织和职权范围是否被禁用----禁止登录
				String powerValidate = "SELECT TORG.STATE FROM (SELECT ORGID FROM TB_USER_ROLE WHERE USERID IN (SELECT ID FROM TB_USER WHERE NAME=?)) TUR"
					+ " LEFT JOIN TB_ORG TORG ON TORG.ID=TUR.ORGID";
				List<Long> orgStatuses = jdbcTemplate.queryForList(powerValidate, Long.class, name);
	            boolean isOutPower=false;
				
	            for (int i = 0; i < orgStatuses.size(); i++) {
	                     if(orgStatuses.get(i)==-1){
	                    	 isOutPower=true;
	                    	 break;
	                     }
				}
				
	            String orgValidate="SELECT TORG.STATE FROM (SELECT ORG FROM TB_USER WHERE NAME=?) TU " +
						" LEFT JOIN TB_ORG TORG ON TORG.ID=TU.ORG";
				List<Long> orgS = jdbcTemplate.queryForList(orgValidate, Long.class, name);
				for(int j=0;j<orgS.size();j++){
					if(orgS.get(j)==-1){
						 isOutPower=true;
						 break;
					}
				}
				
				if(isOutPower){
					u = new User();
					u.setExp(new Exp("登录失败,所属组织（职权范围）已禁用！"));
					return u;
				}
				
				u = list.remove(0);
				List<UserRole> sl = getRoleList(u.getId());
				if (sl != null && sl.size() > 0) {
					u.setRoleList(sl);
					u.setCurrRole(sl.get(0).getRole());
					u.setCurrOrg(sl.get(0).getOrgs().get(0));
					if (u.getCurrOrg().getLevels() == 5) {
						String f = jdbcTemplate.queryForObject("select name from tb_org where id=(select filiale from tb_point where id=" + u.getCurrOrg().getId() + ")", String.class);
						String agency = jdbcTemplate.queryForObject("select name from tb_org where id=(select agency  from tb_point where id=" + u.getCurrOrg().getId() + ")", String.class);
						String area = jdbcTemplate.queryForObject("select name from tb_org where id=(select area from tb_point where id=" + u.getCurrOrg().getId() + ")", String.class);
//						u.setAgency(agency);
//						u.setArea(area);
//						u.setFiliale(f);
						
						/*long fgs = jdbcTemplate.queryForObject("select filiale from tb_point where id=" +u.getCurrOrg().getId()  , Long.class);
						List<Long> zeroSalesTimefList = jdbcTemplate.queryForList("select LINGXLTIME from TB_PARAM where ORG=" + fgs, Long.class);
						Long zeroSalesTimez = jdbcTemplate.queryForObject("select LINGXLTIME from TB_PARAM where ORG=1", Long.class);
						long zeroSalesTimef=0,zeroSalesTime=0;
						if(zeroSalesTimefList.size()==0){
							zeroSalesTimef=0;
						}else if(zeroSalesTimefList.size()==1){
							zeroSalesTimef=zeroSalesTimefList.get(0);
						}
						
						zeroSalesTime = zeroSalesTimef!=0?zeroSalesTimef:zeroSalesTimez;
						if( zeroSalesTime!=0){
							System.out.print(zeroSalesTime);
							Date dateOld = new Date(zeroSalesTime-8*60*60*1000); 
							String zeroTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateOld);
							u.setZeroSalesTime(zeroTime);
						}*/
						
					}else if(u.getCurrOrg().getLevels() == 4){
						String filiale = orgDao.getCurOrgParentName(u.getCurrOrg().getId(),2);
						String agency = orgDao.getCurOrgParentName(u.getCurrOrg().getId(),3);
//						u.setArea(u.getCurrOrg().getName());
//						u.setAgency(agency);
//						u.setFiliale(filiale);
					}else if(u.getCurrOrg().getLevels() == 3){
						String filiale = orgDao.getCurOrgParentName(u.getCurrOrg().getId(),2);
//						u.setAgency(u.getCurrOrg().getName());
//						u.setFiliale(filiale);
					}else if(u.getCurrOrg().getLevels() == 2){
//						u.setFiliale(u.getCurrOrg().getName());
					}

//					Apk apk = apkDao.getCurrentVersion("HisenseApk");
//					if (apk != null && apk.getExp() == null) {
//						u.setApk_url(apk.getPath());
//						u.setWebVersion(apk.getVersion());
//						System.out.println(apk.getPath() + "  " + apk.getVersion() + "  " + apk.getReportTime());
//					}
				}
			} else {

				String sql = "SELECT DELETED FROM TB_USER WHERE NAME=?";
				List<Long> userList = jdbcTemplate.queryForList(sql, Long.class, name);
            
				if (userList.size() < 1) {
					u = new User();
					u.setExp(new Exp("该用户不存在！"));
				} else if (userList.size() == 1 && userList.get(0) == -1) {
					System.out.println("into。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。");
					u = new User();
					u.setExp(new Exp("该用户已禁用！"));
				} else {
					u = new User();
					u.setExp(new Exp("密码错误！"));
				}
			}
		}

		return u;
	}

	/**
	 * 用户登录检验串号
	 */
	private long checkSiid_(String siid, User u) {
		System.out.println("--u-->" + u);
		if (u.getId() == null) {
			return 0;
		}
		long id = u.getId();
		System.out.println("----siid--->" + siid);
		List<Long> orgs = jdbcTemplate.queryForList("SELECT ORG FROM TB_USER WHERE ID=?", new Object[] { id }, Long.class);
		if (orgs.size() == 0 || orgs == null) {
			return 0;
		}
		System.out.println("---orgs.get(0)--->" + orgs.get(0));
		List<Long> ters = jdbcTemplate.queryForList("SELECT TERMINAL FROM TB_TERMINAL WHERE POINTID = ? AND SALEMAN = ?", new Object[] { orgs.get(0), id }, Long.class);
		if (ters.size() == 0 || ters == null) {
			return 0;
		}
		long tes = ters.get(0);
		System.out.println("-----tes-->" + tes);
		// 根据siid查询terminal
		List<Long> terminals1 = jdbcTemplate.queryForList("SELECT TERMINAL FROM TB_TERMINAL WHERE SIID = ?", new Object[] { siid }, Long.class);
		if (terminals1.size() != 0 && tes == terminals1.get(0)) {
			System.out.println("----terminals1.get(0)--->" + terminals1.get(0));
			return tes;
		} else {
			return 0;
		}

	}
	/**
	 * 用户全部角色列表
	 * 
	 * @param u
	 * @return
	 */
	public List<UserRole> getRoleList(final Long uid) {
		final Map<Long, UserRole> map = new HashMap<Long, UserRole>();
		final List<UserRole> l = new ArrayList<UserRole>();

		List<UserRole> list = jdbcTemplate.query(DB_SQL.USER_ROLE_QUERY_ROLEID_BY_USERID, new RowMapper<UserRole>() {
			@Override
			public UserRole mapRow(ResultSet rs, int arg1) throws SQLException {
				Long roleId = rs.getLong(1);
				Long orgId = rs.getLong(2);

				UserRole ur = map.get(roleId);
				if (ur == null) {
					ur = new UserRole();
					ur.setRole(roleDao.get(roleId));
					ur.setUserid(uid);
					ur.setOrgs(new ArrayList<Org>());

					map.put(roleId, ur);
					l.add(ur);
				}
				ur.getOrgs().add(orgDao.get(orgId));

				return ur;
			}
		}, uid);

		return l;
	}

	/**
	 * 激活用户
	 * 
	 * @param users
	 * @return
	 */
	public int allow(User[] users, String optname) {
		List<Object[]> list = new ArrayList<Object[]>();
		for (User u : users) {
			list.add(new Object[] { u.getId() });
		}

		List<Map<String,Object>> nlist=jdbcTemplate.queryForList("SELECT NAME FROM TB_USER WHERE ID="+users[0].getId()+"");
		Map<String,Object> namap=nlist.get(0);
		jdbcTemplate.update(DB_SQL.USER_MONI_INSE,optname,namap.get("name"),DateUtil.getDTime(new Date()),"激活");

		return jdbcTemplate.batchUpdate(DB_SQL.USER_UPD_BY_ID_ALLOW, list).length;
	}

	/**
	 * 禁止用户
	 * 
	 * @param users
	 * @return
	 */
	public int disallow(User[] users, String optname) {
		List<Object[]> list = new ArrayList<Object[]>();
		for (User u : users) {
			list.add(new Object[] { u.getId() });
		}
		List<Map<String,Object>> nlist=jdbcTemplate.queryForList("SELECT NAME FROM TB_USER WHERE ID="+users[0].getId()+"");
		Map<String,Object> namap=nlist.get(0);
		jdbcTemplate.update(DB_SQL.USER_MONI_INSE,optname,namap.get("name"),DateUtil.getDTime(new Date()),"禁用");

		return jdbcTemplate.batchUpdate(DB_SQL.USER_UPD_BY_ID_DISALLOW, list).length;
	}

	/**
	 * 设定当前角色
	 * 
	 * @param u
	 * @param id
	 * @return
	 */
	public Role setRole(User u, Long id) {
		for (UserRole ur : u.getRoleList()) {
			if (ur.getRole().getId().equals(id)) {
				u.setCurrRole(ur.getRole());
				u.setCurrOrg(ur.getOrgs().get(0));
				return ur.getRole();
			}
		}
		return null;
	}

	/**
	 * 设定当前Org
	 * 
	 * @param u
	 * @param id
	 * @return
	 */
	public Org setOrg(User u, Long id) {

		Long rid = u.getCurrRole().getId();
		for (UserRole ur : u.getRoleList()) {
			if (ur.getRole().getId().equals(rid)) {
				for (Org org : ur.getOrgs()) {
					if (id.equals(org.getId())) {
						return org;
					}
				}
			}
		}

		return null;
	}

	/**
	 * 添加用户
	 * 
	 * @param users
	 * @return
	 */
	public GenRes add(User[] users) {
		String sql = DB_SQL.USER_INS;
		String sqJob = DB_SQL.USER_UPD_BY_USERID;
		String sqUserRole = DB_SQL.USER_ROLE_INS;
		GenRes gr = new GenRes();
		List<Object[]> paramList = new ArrayList<Object[]>();
		for (User user : users) {
			List<Long> ids = jdbcTemplate.queryForList("SELECT ID FROM TB_USER WHERE NAME = ?", new Object[] { user.getName().trim() }, Long.class);
			if (ids.size() != 0) {
				gr.setExp(new Exp("用户已存在！"));
				return gr;
			}
//			paramList.add(new Object[] { user.getName().trim(), user.getPass(), user.getOrg(), user.getRealname(), user.getPhoneno(), user.getEmail(), user.getDeStandType(), user.getBrand(), user.getCategory(),user.getOtCategory()});
		}
		/*
		 * DefaultTransactionDefinition def = new
		 * DefaultTransactionDefinition(); DataSourceTransactionManager
		 * transactionManager = new
		 * DataSourceTransactionManager(jdbcTemplate.getDataSource());
		 * TransactionStatus status = transactionManager.getTransaction(def);
		 */
		try {
			if (users[0].getOrg() == null || users[0].getOrg() == 0) {

				gr.setExp(new Exp("添加用户异常！"));
				return gr;
			}

			int[] rows = jdbcTemplate.batchUpdate(sql, paramList);// 插入数据到tb_user

			/*String sqlr = "SELECT R.ID,R.NAME,R.LEVELS FROM TB_ROLE R JOIN (SELECT * FROM TB_ORG ) O ON R.LEVELS=O.LEVELS AND O.ID=?";// 获取roleId，roleName
			List<User> uInfo = jdbcTemplate.query(sqlr, new Object[] { users[0].getOrg() }, new RowMapper<User>() {

				@Override
				public User mapRow(ResultSet rs, int arg1) throws SQLException {

					User user = new User();
					user.setId(rs.getLong(1));
					user.setName(rs.getString(2));
					user.setLevels(rs.getLong(3));
					return user;
				}
			});

			String job = "";
			long roleId = 0;
			System.out.println(uInfo.size());
			for (int i=0;i<uInfo.size();i++) {
				
				if(uInfo.get(i).getLevels()==5){
					job = "促销员";
					roleId = 101;
					break;
				}
			    if(uInfo.get(i).getLevels()!=5  ){
					job = uInfo.get(i).getName();
					roleId = uInfo.get(i).getId();
					break;
				}
			
					
			}*/

			Map<String, Object> jobMap = jdbcTemplate.queryForMap("SELECT NAME FROM TB_ROLE WHERE ID=?", users[0].getRoleId());

			List<Long> userIds = jdbcTemplate.queryForList("Select id from tb_user where name=?", Long.class, users[0].getName());

			long userId = userIds.size() > 0 ? userIds.get(0) : null;

			int update = jdbcTemplate.update(sqJob, jobMap.get("NAME"), userId);// 从tb_role表获取roleName赋给tb_user的job

			int userRoleIns = jdbcTemplate.update(sqUserRole, userId, users[0].getRoleId(), users[0].getOrg(), "THE JOB");// 更新tb_user_role表

			if (rows.length != userRoleIns) {
				gr.setExp(new Exp("添加用户异常！"));
				gr.setPint(0);
				return gr;
			} else {
				gr.setPint(rows.length);
			}

			// transactionManager.commit(status);
		} catch (Exception e) {
			// 确定是否有有效的事务存在，存在回滚
			// transactionManager.rollback(status);
			e.printStackTrace();
			gr.setExp(new Exp("添加用户异常！"));
			gr.setPint(0);
			return gr;
		}
		return gr;
	}

	/**
	 * 删除用户
	 * 
	 * @param ids
	 * @return
	 */
	public int delete(long[] ids) {
		String sql = DB_SQL.USER_DEL_BY_ID;
		List<Object[]> paramList = new ArrayList<Object[]>();
		for (long id : ids) {
			paramList.add(new Object[] { id });
		}
		int[] rows = jdbcTemplate.batchUpdate(sql, paramList);

		int p = 0;
		for (int x : rows) {
			if (x == 1) {
				p++;
			}
		}
		System.out.println("delete user 执行：" + p);
		return p;
	}
	/**
	 * 更新用户信息
	 * 
	 * @param users
	 * @return
	 */
	public GenRes update(User[] users) {
		GenRes gRes = new GenRes();
		String sql = DB_SQL.USER_UPD_BY_ID;
		List<Object[]> paramList = new ArrayList<Object[]>();
		List<Object[]> paramList1 = new ArrayList<Object[]>();
		try {
			for (User user : users) {
//				paramList.add(new Object[] { user.getName(), user.getOrg(), user.getRealname(), user.getPass(), user.getPhoneno(), user.getEmail(), user.getDeStandType(), user.getBrand(), user.getCategory(),user.getOtCategory(),
//						user.getId() });
				paramList1.add(new Object[] { user.getOrg(), user.getId(), user.getId() });
			}
			jdbcTemplate.batchUpdate("update tb_user_role set orgid=? where userid=? and orgid=(select org from tb_user where id=?)", paramList1);
			int[] rows = jdbcTemplate.batchUpdate(sql, paramList);

			gRes.setPint(rows.length);

		} catch (Exception e) {
			e.printStackTrace();
			if (e.getMessage().contains("ORA-00001"))
				gRes.setExp(new Exp("用户名重复！"));
			else
				gRes.setExp(new Exp("修改失败，后台异常！"));
		}
		return gRes;
	}

	/**
	 * 更新用户密码
	 * 
	 * @param users
	 * @return
	 */
	public GenRes updatePass(User[] users) {
		GenRes gRes = new GenRes();
		String sql = DB_SQL.USER_UPD_PASS_BY_ID;
		List<Object[]> paramList = new ArrayList<Object[]>();
		for (User user : users) {
			paramList.add(new Object[] { user.getPass(), user.getId() });
		}
		int[] batchUpdate = jdbcTemplate.batchUpdate(sql, paramList);
		if (batchUpdate.length > 0) {
			gRes.setPint(1);
			return gRes;
		} else {
			gRes.setExp(new Exp("修改密码失败！"));
			return gRes;
		}
	}

	/**
	 * 更新用户角色
	 * 
	 * @param users
	 * @return
	 */
	public int updateRoles(UserRole[] urs) {
		List<Object[]> paramList = new ArrayList<Object[]>();
		for (UserRole ur : urs) {
			for (Org org : ur.getOrgs()) {
				paramList.add(new Object[] { ur.getUserid(), ur.getRole().getId(), org.getId(), "" });
			}
		}
		
	/*	System.out.println("-----------begin-------------");
		for(int i =0;  i<paramList.size();i++){
			System.out.println("-----------sub-------------");
			Object[] obj = (Object[])paramList.get(i);
			if(obj != null)
			System.out.println(obj[0]+"====="+ obj[1]+"=================="+obj[2]);
			System.out.println("-----------sub-------end------------");
		}*/
		jdbcTemplate.update(DB_SQL.USER_UPD_BY_USERID, urs[0].getRole().getName(), urs[0].getUserid());
		jdbcTemplate.update(DB_SQL.USER_ROLE_DEL_BY_USERID, urs[0].getUserid());
		return jdbcTemplate.batchUpdate(DB_SQL.USER_ROLE_INS, paramList).length;
	}
	/**
	 * 根据id获取用户
	 * 
	 * @param id
	 * @return
	 */
	public User query(long id) {
		String sql = DB_SQL.USER_QUERY_BY_ID;
		List<User> userList = jdbcTemplate.query(sql, new Object[] { id }, rowMapper);
		return userList == null ? null : userList.get(0);
	}

	/**
	 * 根据orgid获取(所有 包括下级)用户列表
	 */
	public UserWrapper searchUser(UserCondition con) {
		String sql = DB_SQL.USER_QUERY_BY_ORG;

		List<Object> paramList = new ArrayList<Object>();
		paramList.add(con.getOrgId());
		if(con.getName()!=null && !"".equals(con.getName())){
			sql += " WHERE a.NAME LIKE ?";
			paramList.add("%" + con.getName() + "%");
		}
		if(con.getRealname()!=null && !"".equals(con.getRealname())){
			sql += " WHERE a.REALNAME LIKE ?";
			paramList.add("%" + con.getRealname() + "%");
		}

		UserWrapper wrapper = new UserWrapper();
		try {
			Pager p = con.getP();
			if (null == p) {
				p = new Pager();
			}
			p.resetTotalR(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + sql + ")", paramList.toArray()));
			List<User> lists = jdbcTemplate.query(DB_SQL.getPageSQL(sql), p.queryParam(paramList), new RowMapper<User>() {

				@Override
				public User mapRow(ResultSet rs, int arg1) throws SQLException {
					User user = new User();
					user.setId(rs.getLong("id"));
					user.setName(rs.getString("name"));
//					user.setPass(rs.getString("pass"));
					user.setOrg(rs.getLong("org"));
//					user.setOrgname(rs.getString("orgname"));
					user.setRealname(rs.getString("realname"));
					user.setPhoneno(rs.getString("phoneno"));
					user.setEmail(rs.getString("email"));
//					user.setMobile(rs.getLong("MOBILE"));
//					user.setDeleted(rs.getInt("deleted"));

					return user;
				}

			});
			User[] users = new User[lists.size()];
			for (int i = 0; i < lists.size(); i++) {
				users[i] = lists.get(i);
			}
			wrapper.setP(p);
			wrapper.setUsers(users);
		} catch (Exception e) {
			e.printStackTrace();
			wrapper.setExp(new Exp("数据查询异常！"));
		}
		return wrapper;
	}
	/**
	 * 根据左边组织树导出用户列表
	 */
	public GenRes exportUserByOrg(UserCondition con, String filePath, String retPath) {
		GenRes gr = new GenRes();
		int levels = con.getLevels();
		String sql = "";
		if (levels == 2) {
			sql = "SELECT * FROM (SELECT a.*,b.name as orgname,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
			+ " TORGGG.NAME FILIALE FROM TB_USER a " 
			+ " join (select * from (select * from TB_ORG START WITH ID =" + con.getOrgId()
			+ " CONNECT BY parentid = PRIOR ID )where levels =2 or levels=3 or levels=5) b  on a.org=b.id "
			+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID "
			+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID " 
			+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID "
			+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID )"
			+ " where 1=1";
		}
		/*else if (levels == 3) {// 办事处

			sql = "SELECT * FROM(SELECT a.*,b.name as orgname,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
					+ " TORGGG.NAME FILIALE FROM TB_USER a "
					+ " join (select * from (SELECT * FROM TB_ORG START WITH ID =" + con.getOrgId()
					+ " CONNECT BY parentid = PRIOR ID) where levels=3 or levels =5) b on a.org=b.id " 
					+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID "
					+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID)" 
					+ " WHERE 1=1";
		}*/else {
				// 全部人员
				sql = "SELECT * FROM (SELECT a.*,b.name as orgname,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
						+ " TORGGG.NAME FILIALE FROM TB_USER a " 
						+ " join (select * from (select * from TB_ORG START WITH ID =" + con.getOrgId()
						+ " CONNECT BY parentid = PRIOR ID )where levels =2 or levels=3 or levels=5) b  on a.org=b.id "
						+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID "
						+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID " 
						+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID "
						+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID )"
						+ " where 1=1";
		}
		List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql);

		ExcelUtil excelUtil = new ExcelUtil();
		Workbook wb = excelUtil.openTemplate(filePath + File.separator + "expUser.xls");
		if(wb == null){
			gr.setExp(new Exp("系统没有找到相应的模版！"));
			System.out.println("导出失败————————————>");
			return gr;
		}
		wb.setForceFormulaRecalculation(true);

		for (int i = 0; i < retList.size(); i++) {
			Map<String, Object> retMap = retList.get(i);

			excelUtil.write2(0, i + 1, 0, retMap.get("NAME"), wb);
			excelUtil.write2(0, i + 1, 1, retMap.get("REALNAME"), wb);
			excelUtil.write2(0, i + 1, 2, retMap.get("ORGCODE"), wb);
			excelUtil.write2(0, i + 1, 3, retMap.get("ORGNAME"), wb);
			excelUtil.write2(0, i + 1, 4, retMap.get("JOB"), wb);
			excelUtil.write2(0, i + 1, 5, retMap.get("MOBILE"), wb);
			excelUtil.write2(0, i + 1, 6, retMap.get("EMAIL"), wb);
			excelUtil.write2(0, i + 1, 7, getValue(retMap.get("DESTANDTYPE"), "STANDTYPE"), wb);
			excelUtil.write2(0, i + 1, 8, getCacsValue(retMap.get("DEBRAND"), "BRAND"), wb);
			excelUtil.write2(0, i + 1, 9, getCacsValue(retMap.get("DECATEGORY"), "BRAND_CATEGORY"), wb);
			excelUtil.write2(0, i + 1, 10, retMap.get("OTCATEGORY"), wb);
			String competlog = "禁用";
			if (retMap.get("DELETED") != null)
				if (((BigDecimal) retMap.get("DELETED")).intValue() == 1)
					competlog = "正常";
			excelUtil.write2(0, i + 1, 11, competlog, wb);

			if (levels == 2) {// 总部
				if (retMap.get("JOB")!=null&&retMap.get("JOB").equals("分公司管理员")) {
					excelUtil.write2(0, i + 1, 12, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 13, "_", wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				} else if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))) {
					excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 13, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				    }else{
					 excelUtil.write2(0, i + 1, 12, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 13, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 14, retMap.get("AREA"), wb);
				 }
			} else if (levels == 3) {// 办事处
				if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))) {
					excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 13, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				}else{
					 excelUtil.write2(0, i + 1, 12, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 13, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 14, retMap.get("AREA"), wb);
				 }
			} else {// 总部
				if (retMap.get("JOB")!=null&&retMap.get("JOB").equals("分公司管理员")) {
					excelUtil.write2(0, i + 1, 12, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 13, "_", wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				} else if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))) {
					excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 13, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				    }else{
					 excelUtil.write2(0, i + 1, 12, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 13, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 14, retMap.get("AREA"), wb);
				 }
			}
		}
		String str = DateUtil.getFullStr(new Date());

		String fileName = "expUser" + str + ".xls";
		try {
			String localHostIp = LocalHostIp.getLocalHostAddress().substring(1);
	//		String fileIp = dictDao.getByName("FILEIP").getKV().get(1);
			String fileIp=null;
			String savePath = "";
			if (null == fileIp) {
				savePath = retPath + File.separator + fileName;
			} else if (fileIp.equals(localHostIp)) {
				savePath = retPath + File.separator + fileName;
			} else {
				savePath = "\\\\" + fileIp + "/export" + File.separator + fileName;
			}
			System.out.println(savePath);
			excelUtil.saveIn(wb, savePath);
			System.out.println("导出成功————————————>");
			gr.setPint(retList.size());
			gr.setPstr(fileName);
			return gr;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("导出失败————————————>");
			gr.setExp(new Exp("操作失败！"));
			return gr;
		}

	}

	/**
	 * 根据 门店 分公司模糊查询 用户列表
	 */
	public UserWrapper userByPFname(UserCondition con) {
		UserWrapper wrapper = new UserWrapper();
		Pager p = con.getP();
		String temp="";
		if (null == p) {
			p = new Pager();
		}

		String sql = "SELECT a.*,b.name as orgname ,b.levels FROM TB_USER a join tb_org b on a.org=b.id where 1=1";

		if(con.getRoleId()!=0){//分角色查询
			sql = "SELECT distinct a.*,b.name as orgname ,b.levels FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID ="+con.getOrgId()+" CONNECT BY parentid = PRIOR ID) b on a.org=b.id join tb_user_role d on a.id=d.userid and d.roleid=" +con.getRoleId() + "";
			if(con.getFiliale()!=null&&!con.getFiliale().trim().equals("")){
				sql = "SELECT distinct a.*,b.name as orgname,b.levels FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID = " 
						+ "(select id from tb_org where name like '%"+ con.getFiliale().trim()+"%' and levels=2) CONNECT BY parentid = PRIOR ID) b " 
					 	+ "on a.org=b.id  join tb_user_role c on a.id=c.userid and c.roleid=" +con.getRoleId() + " where 1=1";
				if(con.getPoint() != null && !con.getPoint().trim().equals("")){
					sql="SELECT distinct a.*,b.name as orgname,b.levels FROM TB_USER a join (select * from " 
							+ "(SELECT * FROM TB_ORG START WITH ID = " 
							+ "(select id from tb_org where name like '%" + con.getFiliale().trim()
							+ "%' and levels=2) CONNECT BY parentid = PRIOR ID) where name like'%"+ con.getPoint().trim() + "%' and levels=5) b on a.org=b.id" 
							+ "  join tb_user_role c on a.id=c.userid and c.roleid=" +con.getRoleId() + " where 1=1";
				}	
			}else if (con.getPoint() != null && !con.getPoint().trim().equals("")){
				sql="SELECT distinct a.*,b.name as orgname,b.levels FROM TB_USER a join (select * from tb_org  where levels=5 and name like'%" + con.getPoint().trim() + "%')b on a.org=b.id"
				+ "  join tb_user_role c on a.id=c.userid and c.roleid=" +con.getRoleId() + " where 1=1";
				}
			/*if(con.getAgency()!=null&&!con.getAgency().trim().equals("")){
					sql = "SELECT distinct a.*,b.name as orgname,b.levels FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID = " 
							+ "(select id from tb_org where name='"+ con.getAgency().trim()+ "' and levels=3) CONNECT BY parentid = PRIOR ID) b " 
						 	+ "on a.org=b.id  join tb_user_role c on a.id=c.userid and c.roleid=" +con.getRoleId() + " where 1=1";
					if(con.getPoint() != null && !con.getPoint().trim().equals("")){
						sql="SELECT distinct a.*,b.name as orgname,b.levels FROM TB_USER a join (select * from " 
								+ "(SELECT * FROM TB_ORG START WITH ID = " 
								+ "(select id from tb_org where name like '%" + con.getAgency().trim()
								+ "%' and levels=3) CONNECT BY parentid = PRIOR ID) where name like'%"+ con.getPoint().trim() + "%' and levels=5) b on a.org=b.id" 
								+ "  join tb_user_role c on a.id=c.userid and c.roleid=" +con.getRoleId() + " where 1=1";
					}
			}*/
		}else{
			if (con.getPoint() != null && !con.getPoint().trim().equals("")) {
			// 分公司+门店模糊查询
				if (con.getFiliale()!=null&&!con.getFiliale().trim().equals("")) {

				sql = "SELECT a.*,b.name as orgname,b.levels FROM TB_USER a join (select * from (SELECT * FROM TB_ORG START WITH ID = (select id from tb_org where name like'%" + con.getFiliale().trim()
						+ "%' and levels=2) CONNECT BY parentid = PRIOR ID) where name like'%" + con.getPoint().trim() + "%' and levels=5) b on a.org=b.id where 1=1";
				} else if (con.getAgency()!=null&&!con.getAgency().trim().equals("")) {// 办事处+门店模糊查询

					sql = "SELECT a.*,b.name as orgname,b.levels FROM TB_USER a join (select * from (SELECT * FROM TB_ORG START WITH ID = (select id from tb_org where name='" + con.getAgency().trim()
						+ "' and levels=3) CONNECT BY parentid = PRIOR ID) where name like'%" + con.getPoint().trim() + "%' and levels=5) b on a.org=b.id where 1=1";

				} else{//门店模糊查询
					sql="SELECT a.*,b.name as orgname, b.levels  FROM TB_USER a join (select * from  tb_org  where levels=5 and name like'%" + con.getPoint().trim() + "%')b on a.org=b.id where 1=1";
				}
			}
			else {// 分公司
				if (con.getFiliale() != null && !con.getFiliale().trim().equals("")) {
					sql = "SELECT a.*,b.name as orgname,b.levels FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID = (select id from tb_org where name like '%"+ con.getFiliale().trim()+"%' and levels=2) CONNECT BY parentid = PRIOR ID) b on a.org=b.id where 1=1";
				} else if (con.getAgency()!=null && !con.getAgency().trim().equals("")) {// 办事处
					sql = "SELECT a.*,b.name as orgname,b.levels FROM TB_USER a join (SELECT * FROM TB_ORG START WITH ID = (select id from tb_org where name='" + con.getAgency().trim()
						+ "' and levels=3) CONNECT BY parentid = PRIOR ID) b on a.org=b.id where 1=1";
				}
			}
			
		}
		if (con.getRealname()!=null&&!con.getRealname().trim().equals("")) {// 用户名模糊查询
			 temp =sql+" and  a.name like'%" + con.getRealname().trim() + "%' and b.levels>=" + con.getLevels() + "";
			if(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + temp + ")")>0) sql=temp;
			else sql +=  "and a.realname like'%" + con.getRealname().trim() + "%' and b.levels>=" + con.getLevels() + "";
		}
		
	
		try {
			p.resetTotalR(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + sql + ")"));
			List<Object> paramList = new ArrayList<Object>();
			List<User> lists = jdbcTemplate.query(DB_SQL.getPageSQL(sql), p.queryParam(paramList), new RowMapper<User>() {

				@Override
				public User mapRow(ResultSet rs, int arg1) throws SQLException {
					User user = new User();
					user.setId(rs.getLong("id"));
					user.setName(rs.getString("name"));
					user.setPass(rs.getString("pass"));
					user.setOrg(rs.getLong("org"));
//					user.setOrgname(rs.getString("orgname"));
					user.setRealname(rs.getString("realname"));
					user.setPhoneno(rs.getString("phoneno"));
					user.setEmail(rs.getString("email"));
//					user.setMobile(rs.getLong("MOBILE"));
//					user.setDeleted(rs.getInt("deleted"));
//					user.setDeStandType(rs.getInt("DESTANDTYPE"));
//					user.setBrand(rs.getInt("DEBRAND"));
//					user.setCategory(rs.getInt("DECATEGORY"));
//					user.setOtCategory(rs.getString("OTCATEGORY"));
					return user;
				}

			});
			User[] users = new User[lists.size()];
			for (int i = 0; i < lists.size(); i++) {
				users[i] = lists.get(i);
			}
			wrapper.setP(p);
			wrapper.setUsers(users);
		} catch (Exception e) {
			e.printStackTrace();
			wrapper.setExp(new Exp("数据查询异常！"));
		}
		return wrapper;
	}

	public User[] queryUser(long org) {
		String sql = DB_SQL.USER_QUERY_BY_ORG;
		List<User> lists = jdbcTemplate.query(sql, new Object[] { org }, rowMapper);
		User[] users = new User[lists.size()];
		for (int i = 0; i < lists.size(); i++) {
			users[i] = lists.get(i);
		}
		return users;
	}


	/**
	 * 用户数据导入
	 * 
	 * @param filePath
	 * @return
	 */
	public ImpResult impUsers() {
		List<List<String>> list = new ExcelUtil().read("c:\\users_admin.xls");
		int user = 0;
		int userRole = 0;
		// 初始化角色信息
		// Map<String, Long> roleMap = new HashMap<String, Long>();
		// List<Role> roles = jdbcTemplate.query(DB_SQL.ROLE_QUERYS, new
		// RowMapper<Role>() {
		// @Override
		// public Role mapRow(ResultSet rs, int rows) throws SQLException {
		// Role r = new Role();
		// r.setId(rs.getLong("ID"));
		// r.setName(rs.getString("NAME"));
		// return r;
		// }
		// });
		// for (Role r : roles) {
		// roleMap.put(r.getName(), r.getId());
		// }

		// 初始化所在组织信息
		// Map<String, Long> orgMap = new HashMap<String, Long>();
		// List<Org> orgs = orgDao.getOrgs();
		// for(Org o : orgs) {
		// orgMap.put(o.getName(), o.getId());
		// }
		System.out.println("-------------->" + list.size());
		for (int i = 0; i < list.size(); i++) {
			List<String> row = list.get(i);
			// String str = row.get(2);
			Object[] objs = new Object[7];
			// 登录用户名用工号
			// objs[0] = "".equals(row.get(9)) ? row.get(4).replaceAll(" ", "")
			// : row.get(3);
			objs[0] = row.get(9);
			// 所属组织
			// if("".equals(row.get(3))&&row.get(1).contains("部")){
			// objs[1] = getIdByName(4, row.get(0));
			// if(objs[1]==null){
			// jdbcTemplate.update("INSERT INTO TB_LOG(ID, ORGCODE, EXCELID,
			// MEMO) VALUES (SEQ_LOG.nextval, ?, ?, ?)", row.get(0), i+1,
			// row.get(5));
			// }
			// }
			// if("".equals(row.get(3))&&row.get(1).contains("办")){
			// objs[1] = getIdByName(5, row.get(1));
			// if(objs[1]==null){
			// jdbcTemplate.update("INSERT INTO TB_LOG(ID, ORGCODE, EXCELID,
			// MEMO) VALUES (SEQ_LOG.nextval, ?, ?, ?)", row.get(1), i+1,
			// row.get(5));
			// }
			// }
			// if(!"".equals(row.get(3))){
			// objs[1] = getIdByName(7,row.get(3));
			// if(objs[1]==null){
			// jdbcTemplate.update("INSERT INTO TB_LOG(ID, ORGCODE, EXCELID,
			// MEMO) VALUES (SEQ_LOG.nextval, ?, ?, ?)", row.get(3), i+1,
			// row.get(5));
			// }
			// }
			// long id = getOrgId(row.get(0), row.get(0), row.get(0));
			try {
				long id = jdbcTemplate.queryForLong("select id from tb_org where name='" + row.get(5).trim() + "'");

				objs[1] = id;
				// 真名
				objs[2] = row.get(10).replaceAll(" ", "");
				// 联系方式
				objs[3] = row.get(14);
				// 状态
				objs[4] = 1;
				// row.get(15).contains("离职")?-1:1;
				// 备注
				objs[5] = null;
				// 职位;
				objs[6] = "促销员";
				// row.get(2);
				// Object[] objs1 = new Object[2];
				// objs1[0] = get().get(str);
				// objs1[1] = objs[1];
				jdbcTemplate.update("INSERT INTO TB_USER(ID, NAME, PASS, ORG, REALNAME, PHONENO, DELETED, MEMO, JOB) VALUES (SEQ_USER.nextval, ?, 123456, ?, ?, ?, ?, ?, ?)", objs);
				user++;

			} catch (Exception e) {
				e.printStackTrace();
				jdbcTemplate.update("INSERT INTO TB_LOG(ID, ORGCODE, EXCELID, MEMO) VALUES (SEQ_LOG.nextval, ?, ?, ?)", row.get(3), i + 1, "门店找不到！");
				continue;
			}
			// try{
			// if(!"".equals(str)&&objs[1]!=null){
			// jdbcTemplate.update("INSERT INTO TB_TEST_ROLE(ID, USERID, ROLEID,
			// ORGID) VALUES
			// (SEQ_TEST_ROLE.nextval,SEQ_USER.currval,?,?)",objs1);
			// userRole++;
			// }
			// }catch(Exception e){
			// e.printStackTrace();
			// jdbcTemplate.update("INSERT INTO TB_LOG(ID, ORGCODE, EXCELID,
			// MEMO) VALUES (SEQ_LOG.nextval, ?, ?, ?)", row.get(3), i+1,
			// "currval");
			// continue;
			// }
		}
		ImpResult ip = new ImpResult();
		System.out.println("------完成----->" + user + "-------->" + userRole);
		ip.setImpNums(user + userRole);
		return ip;
	}

	public Long getIdByName(int levels, String name) {
		List<Long> ids = jdbcTemplate.queryForList("SELECT ID FROM TB_ORG WHERE LEVELS = " + levels + " AND NAME = '" + name + "'", Long.class);
		return ids.size() == 0 ? null : ids.get(0);
	}

	public Map<String, Integer> get() {
		Map<String, Integer> roleMap = new HashMap<String, Integer>();
		roleMap.put("促销员", 101);
		roleMap.put("返聘人员", 102);
		roleMap.put("培训助理", 103);
		roleMap.put("信息员", 104);
		return roleMap;
	}

	public Long getOrgId(String name, String name1, String name2) {
		StringBuffer sql = new StringBuffer("SELECT ID FROM TB_ORG");
		sql.append(" WHERE NAME IN('" + name1 + "','" + name2 + "')");
		List<Long> ids = jdbcTemplate.queryForList(sql.toString(), Long.class);
		if (ids == null || ids.size() == 0) {
			return 1l;
		}
		return ids.get(0);
	}

	/**
	 * 往用户角色表里插入记录
	 */
	public ImpResult impUserRole() {
		int count = 0;
		List<User> users = jdbcTemplate.query("SELECT ID, ORG, JOB FROM TB_USER WHERE JOB='促销员'", new RowMapper<User>() {
			public User mapRow(ResultSet rs, int index) throws SQLException {
				User u = new User();
				u.setId(rs.getLong("ID"));
				u.setOrg(rs.getLong("ORG"));
//				u.setApk_url(rs.getString("JOB"));
				return u;
			}
		});
		System.out.println("-------size--->" + users.size());
		for (int i = 0; i < users.size(); i++) {
			Object[] ob = new Object[3];
			ob[0] = users.get(i).getId();
			ob[2] = users.get(i).getOrg();
			ob[1] = 50;
			jdbcTemplate.update("INSERT INTO TB_USER_ROLE(ID, USERID, ROLEID, ORGID) VALUES (SEQ_USER_ROLE.nextval,?,?,?)", ob);
			count++;
		}
		ImpResult ip = new ImpResult();
		ip.setImpNums(count);
		System.out.println("----------->" + count);
		return ip;
	}

	/**
	 * 导出错误信息
	 */
	public void impError() {
		String sql = "SELECT ORGCODE,EXCELID,MEMO FROM TB_LOG ORDER BY EXCELID";
		List<Log> excels = jdbcTemplate.query(sql, new RowMapper<Log>() {
			public Log mapRow(ResultSet rs, int index) throws SQLException {
				Log log = new Log();
				log.setOrgCode(rs.getString(1));
				log.setExcelId(rs.getLong(2));
				log.setMemo(rs.getString(3));
				return log;
			}
		});
		ExcelUtil eu = new ExcelUtil();
		Workbook wb = eu.openTemplate("c:\\error.xls");
		for (int i = 0; i < excels.size(); i++) {
			eu.write2(0, i + 1, 0, excels.get(i).getExcelId(), wb);
			eu.write2(0, i + 1, 1, excels.get(i).getOrgCode(), wb);
			eu.write2(0, i + 1, 2, excels.get(i).getMemo(), wb);
		}
		try {
			eu.saveIn(wb, "c:\\userError.xls");
			System.out.println("导出成功————————————>");
		} catch (Exception e) {
			System.out.println("导出失败————————————>");
		}
	}

	/**
	 * 门店标准填写
	 */
	public GenRes standard() {
		GenRes gr = new GenRes();
		String str = "SELECT TR3.NAME,TR2.NAME,TR1.NAME,TR.NAME,TR.ORGCODE FROM TB_POINT TP LEFT JOIN TB_ORG TR ON TP.ID=TR.ID LEFT JOIN TB_ORG TR1 ON TP.AREA = TR1.ID LEFT JOIN TB_ORG TR2 ON TP.AGENCY = TR2.ID LEFT JOIN TB_ORG TR3 ON TP.FILIALE = TR3.ID";
		List<Log> lists = jdbcTemplate.query(str, new RowMapper<Log>() {
			public Log mapRow(ResultSet rs, int index) throws SQLException {
				Log log = new Log();
				log.setFiliale(rs.getString(1));
				log.setAgency(rs.getString(2));
				log.setArea(rs.getString(3));
				log.setPoint(rs.getString(4));
				log.setOrgCode(rs.getString(5));
				return log;
			}
		});
		System.out.println("----->" + lists.size());
		ExcelUtil eu = new ExcelUtil();
		Workbook wb = eu.openTemplate("c:\\pointname.xls");
		for (int i = 0; i < lists.size(); i++) {
			eu.write2(0, i + 1, 0, lists.get(i).getFiliale(), wb);
			eu.write2(0, i + 1, 1, lists.get(i).getAgency(), wb);
			eu.write2(0, i + 1, 2, lists.get(i).getArea(), wb);
			eu.write2(0, i + 1, 3, lists.get(i).getPoint(), wb);
			eu.write2(0, i + 1, 4, lists.get(i).getOrgCode(), wb);
		}
		try {
			eu.saveIn(wb, "c:\\门店信息.xls");
			System.out.println("导出成功————————————>");
			gr.setPint(lists.size());
			return gr;
		} catch (Exception e) {
			e.printStackTrace();
			gr.setExp(new Exp("导出失败————————————>"));
			return gr;
		}
	}

	/**
	 * 保存登录信息
	 * 
	 * @param u
	 * @param ip
	 */

	public void saveLoginInfo(User u, String ip, String uuid, boolean loginByPhoneDivice) {

		String loginType = "PC";
//		if (loginByPhoneDivice) {
//			loginType = "MOBILE";
//			Long phoneNum = checkSiid(u.getSiid(), u);
//			System.out.println("手机号-----------------" + phoneNum);
//			ip = phoneNum.toString();
//		}
		Org currOrg = u.getCurrOrg();
		Role currRole = u.getCurrRole();

		String orgName = "";
		String roleName = "";

		if (currOrg != null && currRole != null) {
			orgName = currOrg.getName();
			roleName = currRole.getName();
		}
		if (u.getId() != null && u.getId() != 0) {
			jdbcTemplate
					.update(
							"INSERT INTO TB_LOGIN_INFO(ID,USERID,USERNAME,IPADDRESS,LOGINTYPE,LOGINTIME,LOGINOUTTIME,UUID,ONLINETIME,STATUS,ORGNAME,ROLENAME) VALUES(SEQ_LOGIN_INFO.nextval,?,?,?,?,?,?,?,?,?,?,?)",
							u.getId(), u.getName(), ip, loginType, new java.util.Date(), null, uuid, null, 1, orgName, roleName);
		}

	}

	/**
	 * 更细登录信息
	 * 
	 * @param loginId
	 */
	public void updateLoginInfo(String uuid, Long userId) {
		System.out.println("open---------------------");
		String getSql = "SELECT STATUS FROM TB_LOGIN_INFO WHERE USERID=" + userId;
		List<String> status = jdbcTemplate.queryForList(getSql, String.class);
		int hasLogin = 0;
		if (status.size() >= 2) {
			for (int i = 0; i < status.size(); i++) {
				if (status.get(i).equals("1")) {
					hasLogin = 1;
					break;
				}
			}
		}

		if (StringUtils.isBlank(uuid)) {
			System.out.println("notupdate*******");
		} else {
			if (hasLogin == 1) {
				System.out.println("dao---has---Login-------------" + uuid);
				jdbcTemplate.update("UPDATE TB_LOGIN_INFO T SET T.LOGINOUTTIME=?,T.STATUS=? WHERE T.UUID=?", new java.util.Date(), 0, uuid);
			} else {
				System.out.println("dao---uuid:----------------" + uuid);
				jdbcTemplate.update("UPDATE TB_LOGIN_INFO T SET T.LOGINOUTTIME=?,T.STATUS=? WHERE T.UUID=?", new java.util.Date(), -1, uuid);
			}

		}

	}

	/**
	 * 
	 * @param uuid
	 * @throws ParseException
	 */
	public void query(String uuid) throws ParseException {

		List<String> list = jdbcTemplate.queryForList("SELECT to_char(t.LOGINTIME,'YYYY-MM-DD hh24:mi:ss') from  tb_login_info t where t.uuid='" + uuid + "'", String.class);

		// loginTime
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		java.util.Date logintime = sdf.parse(list.get(0));
		Calendar calendar2 = Calendar.getInstance();
		calendar2.setTime(logintime);
		int loginDay = calendar2.get(Calendar.DAY_OF_MONTH);
		int loginHour = calendar2.get(Calendar.HOUR_OF_DAY);
		int loginMinute = calendar2.get(Calendar.MINUTE);
		int loginmiao = calendar2.get(Calendar.SECOND);

		// loginOut
		Calendar calendar = Calendar.getInstance();
		int loginOutDay = calendar.get(Calendar.DAY_OF_MONTH);
		int loginOutHour = calendar.get(Calendar.HOUR_OF_DAY);
		int loginOutMinute = calendar.get(Calendar.MINUTE);
		int loginOutSecond = calendar.get(Calendar.SECOND);

		String onlineTime = "";

		int day = loginOutDay - loginDay;
		int hour = loginOutHour - loginHour;
		int min = loginOutMinute - loginMinute;
		int second = loginOutSecond - loginmiao;

		if (day == 0) {
			onlineTime += "0天 ";
		} else {
			if (hour > 0) {
				onlineTime += day + "天 ";
			} else {
				onlineTime += day - 1 + "天 ";
			}
		}

		if (hour < 0) {
			onlineTime += 24 - loginHour + loginOutHour + "小时 ";
		} else {
			if (min > 0) {
				onlineTime += hour + "小时 ";
			} else {
				onlineTime += hour - 1 + "小时 ";
			}
		}

		if (min < 0) {
			onlineTime += 60 - loginMinute + loginOutMinute + "分 ";
		} else {
			if (second > 0) {
				onlineTime += min + "分 ";
			} else {
				onlineTime += min - 1 + "分 ";
			}

		}
		if (second < 0) {
			onlineTime += 60 - loginmiao + loginOutSecond + "秒 ";
		} else {
			onlineTime += second + "秒 ";
		}

		jdbcTemplate.update("UPDATE TB_LOGIN_INFO T SET T.ONLINETIME=? WHERE T.UUID='" + uuid + "'", onlineTime);

	}

	/**
	 * 获取在线用户信息
	 * 
	 * @param loginfoCondition
	 * @return
	 */
	public LoginInfoWrapper getOnlineInfo(LoginInfoCondition loginfoCondition) {

		long[] propIds = loginfoCondition.getPropIds();
		String propName = loginfoCondition.getPropName();
		int onlineType = loginfoCondition.getOnlineType();

		Pager p = loginfoCondition.getP();
		if (p == null) {
			p = new Pager();
		}

		List<Object> params = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer("SELECT ID,FILIALE, AGENCY, AREA, SHORTNAME FROM TB_POINT  WHERE 1 = 1 AND STATE=1");
		if (null != propName && null != propIds) {
			sql.append(" AND " + InUtil.getIn(propIds, propName, 1000));
		}
		String namesql = "";
		if (onlineType == 0) {
			StringBuffer unionBuffer = new StringBuffer("");

			if (propName.equals("FILIALE")) {
				StringBuffer bf = new StringBuffer();
				for (long l : propIds) {
					bf.append(l).append(",");
				}
				bf.deleteCharAt(bf.length() - 1);
				unionBuffer.append(bf.toString());
			} 

			namesql = "SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
					+ sql.toString()
					+ ") TP "
					+ " JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG"
					+ " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID "
					+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID "
					+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID "

					+ " UNION "
					+ " SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM "
					+ "(SELECT ORG,ID,NAME FROM TB_USER WHERE ORG IN("
					+ unionBuffer.toString()
					+ ")) TU "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG "
					+ " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID " + " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID " + " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID";

			if (propName.equals("HISENSE")) {
				namesql = " SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM "
						+ "(SELECT ORG,ID,NAME FROM TB_USER) TU"
						+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG"
						+ " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID "
						+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID" + " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID";

			}
			if (propName.equals("AGENCY") || propName.equals("AREA") || propName.equals("ID")) {
				namesql = "SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
						+ sql.toString()
						+ ") TP "
						+ " JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID "
						+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG"
						+ " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID " + " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID " + " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID ";
			}

		} else if (onlineType == 1) {
			StringBuffer unionBuffer = new StringBuffer("");
			if (propName.equals("FILIALE")) {
				StringBuffer bf = new StringBuffer();
				for (long l : propIds) {
					bf.append(l).append(",");
				}
				bf.deleteCharAt(bf.length() - 1);
				unionBuffer.append(bf.toString());
			}

			namesql = "SELECT TUR.ROLEID ROLEID,TL.ROLENAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TL.USERNAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TL.ORGNAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
					+ sql.toString()
					+ ") TP "
					+ " LEFT JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID "
					+ " JOIN (SELECT * from TB_LOGIN_INFO where status=1 ORDER BY LOGINTIME DESC)TL ON TL.USERID=TU.ID "
					+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID "
					+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG"

					+ " UNION SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,"
					+ " TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
					+ " SELECT ORG,ID,NAME FROM TB_USER WHERE ORG IN("
					+ unionBuffer.toString()
					+ ")) TU "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG "
					+ " JOIN (SELECT * from TB_LOGIN_INFO where status=1 ORDER BY LOGINTIME DESC) TL ON TL.USERID=TU.ID "
					+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID "
					+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID  ORDER BY LOGINTIME DESC";

			if (propName.equals("HISENSE")) {
				namesql = " SELECT TUR.ROLEID ROLEID,TL.ROLENAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TL.USERNAME NAMES," + "TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,"
						+ " TL.LOGINOUTTIME LOGINOUTTIME,TL.ORGNAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM" + " (SELECT ORG,ID,NAME FROM TB_USER) TU"
						+ " JOIN (SELECT * from TB_LOGIN_INFO where status=1 ORDER BY LOGINTIME DESC)TL ON TL.USERID=TU.ID" + " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID"
						+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID" + " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG ORDER BY TL.LOGINTIME DESC";

			}
			if (propName.equals("AGENCY") || propName.equals("AREA") || propName.equals("ID")) {
				namesql = "SELECT TUR.ROLEID ROLEID,TL.ROLENAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TL.USERNAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TL.ORGNAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
						+ sql.toString()
						+ ") TP "
						+ " LEFT JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID "
						+ " JOIN (SELECT * from TB_LOGIN_INFO where status=1 ORDER BY LOGINTIME DESC)TL ON TL.USERID=TU.ID "
						+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID "
						+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID " + " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG ORDER BY TL.LOGINTIME DESC";
			}
		}

		else {
			StringBuffer unionBuffer = new StringBuffer("");
			if (propName.equals("FILIALE")) {
				StringBuffer bf = new StringBuffer();
				for (long l : propIds) {
					bf.append(l).append(",");
				}
				bf.deleteCharAt(bf.length() - 1);
				unionBuffer.append(bf.toString());
			} 

			namesql = "SELECT TTT.ROLEID ROLEIDS,TTT.ROLE_NAME ROLE_NAMES,TTT.TORG TORGS,TTT.USER_ID USER_IDS,TTT.NAMES NAMESS,TTT.IPADDRESS IPADDRESSS,"
					+ "TTT.LOGINTYPE LOGINTYPES,TTT.LOGINTIME LOGINTIMES,TTT.LOGINOUTTIME LOGINOUTTIMES,TTT.ORGNAME ORGNAMES,TTT.STATUS STATUSS FROM"
					+ "("
					+ "SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
					+ sql.toString()
					+ ") TP "
					+ " JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID "
					+ " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID "
					+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID "
					+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG)TTT WHERE TTT.STATUS=-1 "
					+ " UNION "
					+ " SELECT TTT.ROLEID ROLEIDS,TTT.ROLE_NAME ROLE_NAMES,TTT.TORG TORGS,TTT.USER_ID USER_IDS,TTT.NAMES NAMESS,TTT.IPADDRESS IPADDRESSS,"
					+ " TTT.LOGINTYPE LOGINTYPES,TTT.LOGINTIME LOGINTIMES,TTT.LOGINOUTTIME LOGINOUTTIMES,TTT.ORGNAME ORGNAMES,TTT.STATUS STATUSS FROM "
					+ " (SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM "
					+ " (SELECT ORG,ID,NAME FROM TB_USER WHERE ORG IN(" + unionBuffer.toString() + ")) TU " + " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID "
					+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID " + " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID "
					+ " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG)TTT WHERE TTT.STATUS=-1 ORDER BY LOGINTIMES DESC";

			if (propName.equals("HISENSE")) {
				namesql = " SELECT TTT.ROLEID ROLEIDS,TTT.ROLE_NAME ROLE_NAMES,TTT.TORG TORGS,TTT.USER_ID USER_IDS,TTT.NAMES NAMESS,TTT.IPADDRESS IPADDRESSS,"
						+ " TTT.LOGINTYPE LOGINTYPES,TTT.LOGINTIME LOGINTIMES,TTT.LOGINOUTTIME LOGINOUTTIMES,TTT.ORGNAME ORGNAMES,TTT.STATUS STATUSS FROM "
						+ " (SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM "
						+ " (SELECT ORG,ID,NAME FROM TB_USER) TU" + " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID" + " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID"
						+ " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID " + " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG)TTT WHERE TTT.STATUS=-1 ORDER BY TL.LOGINTIME DESC";

			}
			if (propName.equals("AGENCY") || propName.equals("AREA") || propName.equals("ID")) {
				namesql = "SELECT TTT.ROLEID ROLEIDS,TTT.ROLE_NAME ROLE_NAMES,TTT.TORG TORGS,TTT.USER_ID USER_IDS,TTT.NAMES NAMESS,TTT.IPADDRESS IPADDRESSS,"
						+ "TTT.LOGINTYPE LOGINTYPES,TTT.LOGINTIME LOGINTIMES,TTT.LOGINOUTTIME LOGINOUTTIMES,TTT.ORGNAME ORGNAMES,TTT.STATUS STATUSS FROM"
						+ "("
						+ "SELECT TUR.ROLEID ROLEID,TR.NAME ROLE_NAME,TU.ORG TORG,TU.ID USER_ID,TU.NAME NAMES,TL.IPADDRESS IPADDRESS,TL.LOGINTYPE LOGINTYPE,TL.LOGINTIME LOGINTIME,TL.LOGINOUTTIME LOGINOUTTIME,TOG.NAME ORGNAME,nvl(TL.STATUS,-1) STATUS FROM("
						+ sql.toString() + ") TP " + " JOIN (SELECT ORG,ID,NAME FROM TB_USER) TU ON TU.ORG=TP.ID " + " LEFT JOIN TB_LOGIN_INFO TL ON TL.USERID=TU.ID "
						+ " LEFT JOIN TB_USER_ROLE TUR ON TUR.USERID=TU.ID " + " LEFT JOIN TB_ROLE TR ON TR.ID=TUR.ROLEID " + " LEFT JOIN TB_ORG TOG ON TOG.ID=TU.ORG)TTT WHERE TTT.STATUS=-1 ";
			}

		}

		p.queryParam(params);
		Integer counts = jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + namesql + ")");
		p.resetTotalR(counts);
		List<LoginInfo> lists = jdbcTemplate.query(DB_SQL.getPageSQL(namesql), p.queryParam(params), new RowMapper<LoginInfo>() {

			@Override
			public LoginInfo mapRow(ResultSet rs, int arg1) throws SQLException {

				LoginInfo loginInfo = new LoginInfo();
				loginInfo.setId(rs.getLong(1));
				loginInfo.setRoleName(rs.getString(2));
				loginInfo.setUsername(rs.getString(5));
				loginInfo.setIpadress(rs.getString(6));
				loginInfo.setLoginType(rs.getString(7));
				loginInfo.setLogintime(rs.getTimestamp(8));
				loginInfo.setLoginouttime(rs.getTimestamp(9));
				loginInfo.setOrgName(rs.getString(10));
				loginInfo.setStatus(rs.getLong(11));
				return loginInfo;
			}
		});

		System.out.println("获取在线信息+++++++++++++++++++++：" + lists.size());
		LoginInfo[] loginInfoDetails = new LoginInfo[lists.size()];

		for (int i = 0; i < lists.size(); i++) {
			loginInfoDetails[i] = lists.get(i);
		}
		LoginInfoWrapper loginInfoWrapper = new LoginInfoWrapper();
		loginInfoWrapper.setLoginInfos(loginInfoDetails);
		loginInfoWrapper.setP(p);

		return loginInfoWrapper;
	}

	/**
	 * 查询用户是否有登录
	 * 
	 * @param u
	 * @return
	 */
	public boolean checkUidExsist(User u, boolean logintype, String ip) {
		String login = "PC";
		if (logintype) {
			login = "MOBILE";
		}

		String sql = "SELECT count(1) FROM TB_LOGIN_INFO T WHERE T.USERID=? AND T.LOGINTYPE=? AND T.IPADDRESS=?";
		int Q = jdbcTemplate.queryForInt(sql, u.getId(), login, ip);
		if (Q > 0) {
			// 如果有记录，先删除该记录
			jdbcTemplate.update("DELETE FROM TB_LOGIN_INFO T WHERE T.USERID=? AND T.LOGINTYPE=? AND T.IPADDRESS=?", u.getId(), login, ip);
			return true;
		} else {
			return false;
		}
	}

	public LoginOutWrapper getLastLoginOutTime(QueryCondition condition) {

		long id = condition.getUserId();
		String sql = "SELECT USERID,LOGINTIME,LOGINOUTTIME FROM TB_LOGIN_INFO T WHERE T.USERID=? ORDER BY T.LOGINTIME DESC";
		List<LoginInfo> query = jdbcTemplate.query(sql, new Object[] { id }, loginOutMapper);
		LoginOutWrapper loginOutWrapper = new LoginOutWrapper();
		LoginInfo loginInfo = new LoginInfo();
		if (query.size() > 2) {
			loginInfo = query.get(1);
			loginOutWrapper.setUserId(loginInfo.getUserid());
			loginOutWrapper.setLoginTime(loginInfo.getLogintime());
			loginOutWrapper.setLogoutTime(loginInfo.getLoginouttime());

		}

		return loginOutWrapper;
	}

	public GenRes refreshTime() {
		GenRes gr = new GenRes();
		String sysSql = DB_SQL.SYSTIME;
		String ts = jdbcTemplate.queryForObject(sysSql, String.class);
		String[] split = ts.split("-");
		StringBuffer sb = new StringBuffer("");
		sb.append("当前时间：" + split[0] + "年" + split[1] + "月" + split[2] + "日" + "  " + split[3] + ":" + split[4]);
		gr.setPstr(sb.toString().length() > 0 ? sb.toString() : "");
		return gr;
	}

	/*
	 * 导入用户
	 * 
	 * @param request
	 * 
	 * @param response
	 * 
	 * @return
	 */
	public String impUser(HttpServletRequest request, HttpServletResponse response) {
		long userId = (Long) request.getSession().getAttribute("userId");

		String roleSql = "SELECT TRL.NAME FROM (SELECT ROLEID FROM TB_USER_ROLE WHERE USERID=?) TR LEFT JOIN TB_ROLE TRL ON TRL.ID=TR.ROLEID";
		List<String> list = jdbcTemplate.queryForList(roleSql, String.class, userId);

		if (list.size() > 0 && list.get(0).equals("分公司管理员")) {

		} else {

		}

		return null;
	}

	/**
	 * 导入用户信息
	 * 
	 * @param rfn
	 * @param request
	 * @return
	 */
	public GenRes impUser(String rfn, HttpServletRequest request) {

		StringBuilder errorMsg = new StringBuilder("");
		long userId = (Long) request.getSession().getAttribute("userId");
		// 是否总部
		boolean isZb = true;
		// 是否办事处
		boolean isAgency = false;

		String roleSql = "SELECT TRL.NAME FROM (SELECT ROLEID FROM TB_USER_ROLE WHERE USERID=?) TR LEFT JOIN TB_ROLE TRL ON TRL.ID=TR.ROLEID";
		List<String> list = jdbcTemplate.queryForList(roleSql, String.class, userId);

		if (list.size() > 0 && list.get(0).equals("分公司管理员")) {
			isZb = false;
		}

		String queryFilialeForAgency = "SELECT TORG.NAME FROM (SELECT ID,PARENTID FROM TB_ORG WHERE ID=?) TOR LEFT JOIN TB_ORG TORG ON TORG.ID=TOR.PARENTID";
		List<String> filialsForAgency = jdbcTemplate.queryForList(queryFilialeForAgency, String.class, userId);

		String filialeOfAgency = "";

		String queryAgencyName = "SELECT NAME FROM TB_ORG WHERE ID=(SELECT ORG FROM TB_USER WHERE ID=?)";
		List<String> agencyS = jdbcTemplate.queryForList(queryAgencyName, String.class, userId);
		// 办事处名称
		String agencyName = "";

		if (agencyS.size() > 0) {
			agencyName = agencyS.get(0);
		}

		if (filialsForAgency.size() > 0) {
			filialeOfAgency = filialsForAgency.get(0);
		}

		if (list.size() > 0 && list.get(0).equals("办事处管理员")) {
			isAgency = true;
		}
		GenRes genRes = new GenRes();
		List<List<String>> rows = new ExcelUtil().read(rfn);

		List<String> baseLine = rows.get(0);
		String first = baseLine.get(0);
		System.out.println(rows.get(0).size());
		List<String> nameList = new ArrayList<String>();
		if (!first.trim().equals("分公司*必填")) {
			genRes.setExp(new Exp("请下载最新模版！"));
			return genRes;
		}
		// 读取上传文件
		String insertSql = "INSERT INTO TB_USER(ID,NAME,PASS,ORG,REALNAME,PHONENO,EMAIL,DELETED,DESTANDTYPE,DEBRAND,DECATEGORY,JOB,OTCATEGORY) VALUES(SEQ_USER.nextval,?,?,?,?,?,?,1,?,?,?,?,?)";
		List<Object[]> params = new ArrayList<Object[]>();
		List<Object[]> params2 = new ArrayList<Object[]>();
		// 分公司
		String filialeSql = "SELECT NAME FROM TB_ORG WHERE LEVELS=2";
		List<String> filials = jdbcTemplate.queryForList(filialeSql, String.class);
		// 办事处
		String agencySql = "SELECT NAME FROM TB_ORG WHERE LEVELS=3";
		List<String> agencys = jdbcTemplate.queryForList(agencySql, String.class);
		// 片区
		String areaSql = "SELECT NAME FROM TB_ORG WHERE LEVELS=4";
		List<String> areas = jdbcTemplate.queryForList(areaSql, String.class);

		// 展区
		String standTypeSql = "SELECT EVALUE FROM TB_DICT WHERE NAME='STANDTYPE'";
		List<String> standTypes = jdbcTemplate.queryForList(standTypeSql, String.class);

		// 品牌
		String brandSql = "SELECT EVALUE FROM TB_CASC WHERE NAME='BRAND'";
		List<String> brands = jdbcTemplate.queryForList(brandSql, String.class);
		// 品类
		String categorySql = "SELECT EVALUE FROM TB_CASC WHERE NAME='BRAND_CATEGORY'";
		List<String> categorys = jdbcTemplate.queryForList(categorySql, String.class);

		String getId = "SELECT ID FROM TB_ORG WHERE NAME=? AND LEVELS=5";

		long updateUserQuatity = 0;

		String userIdSqlString = "SELECT ID FROM TB_USER WHERE NAME=?";
		for (int i = 1; i < rows.size(); i++) {

			long roleId = 0;
			int standTypeKey = 0;
			long pointId = 0;
			int brandKey=0;
			int categoryKey = 0;
			boolean isUpdateUser = false;

			List<String> row = rows.get(i);
			Object[] objs = new Object[11];
			Object[] objs2= new Object[1];
			String filiale = row.get(0).trim();
			String agency = row.get(1).trim();
			String area = row.get(2).trim();
			String pointName = row.get(3).trim();
			String role = row.get(5).trim();
			String userName = row.get(6).trim();
			String name = row.get(7).trim();
			String standType = row.get(8);
			String brand = row.get(9);
			String category = row.get(10);
			String otCategory = row.get(11).trim();
			String mobile = row.get(12).trim();
			String email = row.get(13).trim();

			if (filiale.trim().equals("") || StringUtils.isBlank(filiale)) {
				errorMsg.append("第" + (i + 1) + "行，分公司不能为空！").append("\t\n");
				continue;
			} else {
				// 验证分公司
				if (isZb) {
					if (!filials.contains(filiale)) {
						errorMsg.append("第" + (i + 1) + "行，分公司不存在!").append("\t\n");
						continue;
					}
				} else if (isAgency) {// 办事处

					if (!filiale.trim().equals(filialeOfAgency)) {
						errorMsg.append("第" + (i + 1) + "行，没有权限导入其他所属分公司下用户！").append("\t\n");
					}

				} else {
					String getFilialeString = "SELECT TORG.NAME FROM (SELECT ID,ORG FROM TB_USER WHERE ID=?) TU LEFT JOIN TB_ORG TORG ON TORG.ID=TU.ORG";
					List<String> filiales = jdbcTemplate.queryForList(getFilialeString, String.class, userId);

					if (filiales.size() > 0 && !filiales.get(0).equals(filiale.trim())) {
						errorMsg.append("第" + (i + 1) + "行，不能导入其它分公司的用户!").append("\t\n");
						continue;
					}
				}

			}

			if (agency.trim().equals("") || StringUtils.isBlank(agency)) {
				errorMsg.append("第" + (i + 1) + "行，办事处不能为空！").append("\t\n");
				continue;
			} else {
				// 验证办事处
				if (!agencys.contains(agency)) {
					errorMsg.append("第" + (i + 1) + "行，该办事处不存在!").append("\t\n");
					continue;
				} else {
					if (isAgency) {
						if (!agencyName.equals(agency.trim())) {
							errorMsg.append("第" + (i + 1) + "行，没有权限导入其他办事处人员！").append("\t\n");
						}
					} else {
						String agencyYz = "SELECT NAME FROM TB_ORG WHERE ID=(SELECT PARENTID FROM TB_ORG WHERE NAME=?)";
						List<String> filia = jdbcTemplate.queryForList(agencyYz, new Object[] { agency.trim() }, String.class);
						if (filia.size() > 0) {
							if (!filia.get(0).equals(filiale)) {
								errorMsg.append("第" + (i + 1) + "行，没有权限导入其他分公司办事处的人员信息!").append("\t\n");
								continue;
							}
						}
					}

				}
			}

			if (area.trim().equals("") || StringUtils.isBlank(area)) {
				errorMsg.append("第" + (i + 1) + "行，片区不能为空！").append("\t\n");
				continue;
			} else {
				// 验证片区
				if (!areas.contains(area)) {
					errorMsg.append("第" + (i + 1) + "行，该片区不存在!").append("\t\n");
					continue;
				} else {
					String agencyYz = "SELECT NAME FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE NAME=?)";
					List<String> agencyy = jdbcTemplate.queryForList(agencyYz, new Object[] { area.trim() }, String.class);
					if (agencyy.size() > 0) {
						if (!agencyy.get(0).equals(agency.trim())) {
							errorMsg.append("第" + (i + 1) + "行，办事处与片区不匹配!").append("\t\n");
							continue;
						}
					}
				}
			}

			if (pointName.trim().equals("") || StringUtils.isBlank(pointName)) {
				errorMsg.append("第" + (i + 1) + "行，门店不能为空！").append("\t\n");
				continue;
			} else {
				// 验证门店有无
				List<Long> points = jdbcTemplate.queryForList(getId, Long.class, pointName);
				if (points.size() > 0) {
					pointId = points.get(0);
				}
				if (pointId == 0) {
					errorMsg.append("第" + (i + 1) + "行，没有此门店，请检查!").append("\t\n");
					continue;
				}
				// 验证门店
				if (isZb) {
					String filialeString = "SELECT TORG.NAME FROM (SELECT FILIALE FROM TB_POINT WHERE ID=?) TP LEFT JOIN TB_ORG TORG ON TORG.ID=TP.FILIALE";
					List<String> pf = jdbcTemplate.queryForList(filialeString, String.class, pointId);
					if (pf.size() > 0) {
						if (!pf.get(0).equals(filiale.trim())) {
							errorMsg.append("第" + (i + 1) + "行，不能导入其他分公司的门店，请检查!").append("\t\n");
						} else {
							String agencyYz = "SELECT NAME FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE NAME=?)";
							List<String> areass = jdbcTemplate.queryForList(agencyYz, new Object[] { pointName.trim() }, String.class);
							if (areass.size() > 0) {
								if (!areass.get(0).equals(area.trim())) {
									errorMsg.append("第" + (i + 1) + "行，门店与片区不匹配!").append("\t\n");
									continue;
								} else {
									objs[2] = pointId;
								}
							}

						}

					}

				} else if (isAgency) {// 办事处

					String areaString = "SELECT TORG.NAME FROM (SELECT AREA FROM TB_POINT WHERE ID=?) TP LEFT JOIN TB_ORG TORG ON TORG.ID=TP.AREA";
					List<String> pa = jdbcTemplate.queryForList(areaString, String.class, pointId);
					if (pa.size() > 0) {

						if (!pa.get(0).equals(area.trim())) {
							errorMsg.append("第" + (i + 1) + "行，不能导入其他片区的门店，请检查!").append("\t\n");
						}

					}

				} else {
					String filialeString = "SELECT TORG.NAME FROM (SELECT FILIALE FROM TB_POINT WHERE ID=?) TP LEFT JOIN TB_ORG TORG ON TORG.ID=TP.FILIALE";
					List<String> pf = jdbcTemplate.queryForList(filialeString, String.class, pointId);
					if (pf.size() > 0) {
						if (!pf.get(0).equals(filiale.trim())) {
							errorMsg.append("第" + (i + 1) + "行，不能导入其他分公司的门店，请检查!").append("\t\n");
						} else {
							String agencyYz = "SELECT NAME FROM TB_ORG WHERE ID IN(SELECT PARENTID FROM TB_ORG WHERE NAME=?)";
							List<String> areass = jdbcTemplate.queryForList(agencyYz, new Object[] { pointName.trim() }, String.class);
							if (areass.size() > 0) {
								if (!areass.get(0).equals(area.trim())) {
									errorMsg.append("第" + (i + 1) + "行，门店与片区不匹配!").append("\t\n");
									continue;
								} else {
									objs[2] = pointId;
								}
							}
						}

					}

				}

			}
			if (role.trim().equals("") || StringUtils.isBlank(role)) {
				errorMsg.append("第" + (i + 1) + "行，人员角色不能为空！").append("\t\n");
				continue;
			} else {
				if (role.trim().equals("促销员")) {
					roleId = 101;
				} else if (role.trim().equals("店长")) {
					roleId = 100;
				} else {
					errorMsg.append("第" + (i + 1) + "行，角色目前只限【促销员】【店长】!").append("\t\n");
					continue;
				}
			}

			if (name.trim().equals("") || StringUtils.isBlank(name)) {
				errorMsg.append("第" + (i + 1) + "行，人员编码不能为空！").append("\t\n");
				continue;
			}

			if (userName.trim().equals("") || StringUtils.isBlank(userName)) {

				errorMsg.append("第" + (i + 1) + "行，人员姓名不能为空！").append("\t\n");
				continue;
			}
			String queryDict = "SELECT EKEY FROM TB_DICT WHERE NAME=? AND EVALUE=?";
			String queryBrand = "SELECT EKEY FROM TB_CASC WHERE NAME=? AND EVALUE=?";
			String queryCate ="SELECT EKEY FROM TB_CASC WHERE NAME=? AND EVALUE=? AND PK=?";

			if (StringUtils.isNotBlank(standType.trim())) {
				if (!standTypes.contains(standType)) {
					errorMsg.append("第" + (i + 1) + "行，无此展台类型！类型包括：【综合】【冰箱】【冷柜】【洗衣机】【其他】").append("\t\n");
					continue;
				} else {
					int sta = jdbcTemplate.queryForInt(queryDict, "STANDTYPE", standType.trim());
					standTypeKey = sta;
				}
			} else {
				String standtypeKey = "SELECT EKEY FROM TB_DICT WHERE NAME='STANDTYPE' AND EVALUE='综合'";
				int stand = jdbcTemplate.queryForInt(standtypeKey);
				standTypeKey = stand;

			}

			if (StringUtils.isNotBlank(brand.trim())) {
				if (!brands.contains(brand.trim())) {
					errorMsg.append("第" + (i + 1) + "行，无此品牌！品牌包括：【Niu】【Web】").append("\t\n");
					continue;
				} else {
					brandKey = jdbcTemplate.queryForInt(queryBrand, "BRAND", brand.trim());	
				}
			} else {
				String brandKeySql = "SELECT EKEY FROM TB_CASC WHERE NAME='BRAND' AND EVALUE='容声'";
				brandKey = jdbcTemplate.queryForInt(brandKeySql);
				
			}
			if (StringUtils.isNotBlank(category.trim())) {
				if (!categorys.contains(category.trim())) {
					errorMsg.append("第" + (i + 1) + "行，无此品类！品类包括：【冰箱】【洗衣机】【冷柜】").append("\t\n");
					continue;
				} else {
					categoryKey = jdbcTemplate.queryForInt(queryCate, "BRAND_CATEGORY", category.trim(),brandKey);	
				}
			} else {
				String cateKeySql = "SELECT EKEY FROM TB_CASC WHERE NAME='BRAND_CATEGORY' AND EVALUE='冰箱' AND PK= 2";
				categoryKey = jdbcTemplate.queryForInt(cateKeySql);
				
			}

			// 用户名
			long userIds = 0;
			List<Long> usess = jdbcTemplate.queryForList(userIdSqlString, Long.class, name);
			if (usess.size() > 0) {
				userIds = usess.get(0);
			}
			String updateUserSql = "UPDATE TB_USER SET ORG=?,REALNAME=?,PHONENO=?,EMAIL=?,JOB=? WHERE NAME=?";
			String updateUserRole = "UPDATE TB_USER_ROLE SET ROLEID=?,ORGID=? WHERE USERID=?";
			if (userIds != 0) {
				// 已存在用户！进行更新..
				jdbcTemplate.update(updateUserSql, pointId, userName, mobile, email, role, name);
				jdbcTemplate.update(updateUserRole, roleId, pointId, userIds);
				isUpdateUser = true;
				updateUserQuatity++;
				continue;
			} else {
				objs[0] = name;
				objs2[0] = name;
				nameList.add(name);
			}
			objs[1] = "123456";

			objs[3] = userName;
			objs[4] = mobile;
			objs[5] = email;
			objs[6] = standTypeKey;
			objs[7] = brandKey;
			objs[8] = categoryKey;
			objs[9] = role;
			objs[10]=otCategory;
			params2.add(objs2);
			if (!isUpdateUser) {
				params.add(objs);
			}

		}

		String insertRoleString = "INSERT INTO TB_USER_ROLE VALUES(SEQ_USER_ROLE.NEXTVAL,?,?,?,?)";
		String deUserString ="DELETE FROM TB_USER WHERE NAME =?";
		long roleUpdate = 0;
		if (errorMsg.length() == 0) {
			try {
				int[] insertQuatity = jdbcTemplate.batchUpdate(insertSql, params);
				// 激活用户
				for (int i = 0; i < params.size(); i++) {
					long roleIds = 0;
					Object[] objects = params.get(i);
					if (objects[9].equals("促销员")) {
						roleIds = 101;
					} else {
						roleIds = 100;
					}
					long userIds = jdbcTemplate.queryForLong(userIdSqlString, objects[0]);
					int update = jdbcTemplate.update(insertRoleString, userIds, roleIds, objects[2], "THE JOB");
					roleUpdate += update;

				}
				genRes.setPstr("导入成功！一共导入：" + insertQuatity.length + "条用户记录，成功激活 " + roleUpdate + " 个用户 ;共更新用户信息 " + updateUserQuatity + " 条！");

				genRes.setPstr("导入成功！一共导入：" + insertQuatity.length + "条用户记录，成功激活 " + roleUpdate + " 个用户 ;共更新用户信息 " + updateUserQuatity + " 条！");
			} catch (Exception e) {
				int[] deUser = jdbcTemplate.batchUpdate(deUserString, params2);
				genRes.setExp(new Exp("导入异常！"));
				return genRes;
			}
			return genRes;
		} else {
			genRes.setExp(new Exp(errorMsg.toString()));
			return genRes;
		}

	}

	public GenRes exportUser(UserCondition con, String filePath, String retPath) {
		GenRes gr = new GenRes();
		String temp="";
		int levels = con.getLevels();
		String sql = "";
		if (levels == 2) {// 分公司

			sql = "SELECT * FROM(SELECT a.*,b.name as orgname,b.levels,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
					+ " TORGGG.NAME FILIALE FROM TB_USER a "
					+ " join (select * from (SELECT * FROM TB_ORG START WITH ID =" + con.getOrgId()
					+ " CONNECT BY parentid = PRIOR ID) where levels=2 or levels =3 or levels=5) b on a.org=b.id " 
					+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID "
					+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID "
					+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID ) "
					+ " WHERE 1=1";
			if(con.getRoleId()!=0){
				sql ="SELECT distinct c.* FROM ("+sql+") c" 
						+ " join tb_user_role d on c.id=d.userid and d.roleid=" +con.getRoleId() + "" 
						+ " where 1=1";
			}
			if (con.getRealname()!=null&&!con.getRealname().trim().equals("")) {// 用户名模糊查询
				 temp =sql+" and  name like'%" + con.getRealname().trim() + "%' ";
				if(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + temp + ")")>0) sql=temp;
				else sql +=  "and realname like'%" + con.getRealname().trim() + "%' ";
			}
			if (con.getPoint() != null && !con.getPoint().trim().equals("")) {
				// 模糊查询门店
				sql += "and orgname like '%" + con.getPoint().trim() + "%' and levels=5";
			}
		} /*else if (levels == 3) {// 办事处

			sql = "SELECT * FROM(SELECT a.*,b.name as orgname,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
					+ " TORGGG.NAME FILIALE FROM TB_USER a " + " join (select * from (SELECT * FROM TB_ORG START WITH ID =" + con.getOrgId()
					+ " CONNECT BY parentid = PRIOR ID) where levels=3 or levels =5) b on a.org=b.id " 
					+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID "
					+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID)" 
					+ " WHERE 1=1";
			if(con.getRoleId()!=0){
				sql ="SELECT distinct c.* FROM ("+sql+") c" 
						+ " join tb_user_role d on c.id=d.userid and d.roleid=" +con.getRoleId() + "" 
						+ " where 1=1";
			}
			if (con.getRealname()!=null&&!con.getRealname().trim().equals("")) {// 用户名模糊查询
				 temp =sql+" and  name like'%" + con.getRealname().trim() + "%' ";
				if(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + temp + ")")>0) sql=temp;
				else sql +=  "and realname like'%" + con.getRealname().trim() + "%' ";
			}else if (con.getPoint() != null && !con.getPoint().trim().equals("")) {// 模糊查询门店
				sql += " and orgname like '%" + con.getPoint().trim() + "%' and levels=5";
			}
		}*/ else {
			// 全部人员
			sql = "SELECT * FROM (SELECT a.*,b.name as orgname,b.levels,b.orgcode,TORG.NAME AREA,TORGG.NAME AGENCY ,"
					+ " TORGGG.NAME FILIALE FROM TB_USER a " 
					+ " join (select * from (select * from TB_ORG START WITH ID =" + con.getOrgId()
					+ " CONNECT BY parentid = PRIOR ID )where levels =2 or levels=3 or levels=5) b  on a.org=b.id "
					+ " LEFT JOIN TB_ORG TORG ON TORG.ID=b.PARENTID "
					+ " LEFT JOIN TB_ORG TORGG ON TORGG.ID=TORG.PARENTID " 
					+ " LEFT JOIN TB_ORG TORGGG ON TORGGG.ID=TORGG.PARENTID "
					+ " LEFT JOIN TB_ORG TORGGGG ON TORGGGG.ID=TORGGG.PARENTID)"
					+ " where 1=1";
			if(con.getRoleId()!=0){
				sql ="SELECT distinct c.* FROM ("+sql+") c" 
						+ " join tb_user_role d on c.id=d.userid and d.roleid=" +con.getRoleId() + "" 
						+ " where 1=1";
			}
			if (con.getPoint()!=null&&!con.getPoint().trim().equals("")) { // 模糊查询门店
					sql = sql + "and orgname like '%" + con.getPoint().trim() + "%' and levels=5";
				}
			if (con.getFiliale()!=null&&!con.getFiliale().trim().equals("")) {// 分公司查询
					sql = sql + "and filiale like '%" + con.getFiliale().trim() + "%' or area like '%" + con.getFiliale().trim() + "%' or orgname like '%" + con.getFiliale().trim() + "%'";
				}
			
			if (con.getRealname()!=null&&!con.getRealname().trim().equals("")) {// 用户名模糊查询
				 temp =sql+" and  name like'%" + con.getRealname().trim() + "%' ";
				if(jdbcTemplate.queryForInt("SELECT COUNT(1) FROM (" + temp + ")")>0) sql=temp;
				else sql +=  "and realname like'%" + con.getRealname().trim() + "%' ";
			}
		
		}
		List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql);

		ExcelUtil excelUtil = new ExcelUtil();
		Workbook wb = excelUtil.openTemplate(filePath + File.separator + "expUser.xls");
		if(wb == null){
			gr.setExp(new Exp("系统没有找到相应的模版！"));
			System.out.println("导出失败————————————>");
			return gr;
		}
		wb.setForceFormulaRecalculation(true);

		for (int i = 0; i < retList.size(); i++) {
			Map<String, Object> retMap = retList.get(i);

			excelUtil.write2(0, i + 1, 0, retMap.get("NAME"), wb);
			excelUtil.write2(0, i + 1, 1, retMap.get("REALNAME"), wb);
			excelUtil.write2(0, i + 1, 2, retMap.get("ORGCODE"), wb);
			excelUtil.write2(0, i + 1, 3, retMap.get("ORGNAME"), wb);
			excelUtil.write2(0, i + 1, 4, retMap.get("JOB"), wb);
			excelUtil.write2(0, i + 1, 5, retMap.get("PHONENO"), wb);
			excelUtil.write2(0, i + 1, 6, retMap.get("EMAIL"), wb);
			excelUtil.write2(0, i + 1, 7, getValue(retMap.get("DESTANDTYPE"), "STANDTYPE"), wb);
			excelUtil.write2(0, i + 1, 8, getCacsValue(retMap.get("DEBRAND"), "BRAND"), wb);
			excelUtil.write2(0, i + 1, 9, getCacsValue(retMap.get("DECATEGORY"), "BRAND_CATEGORY"), wb);
			excelUtil.write2(0, i + 1, 10, retMap.get("OTCATEGORY"), wb);
			String competlog = "禁用";
			if (retMap.get("DELETED") != null)
				if (((BigDecimal) retMap.get("DELETED")).intValue() == 1)
					competlog = "正常";
			excelUtil.write2(0, i + 1, 11, competlog, wb);

			if (levels == 2) {// 总部
				if (retMap.get("JOB")!=null&&retMap.get("JOB").equals("分公司管理员")) {
					excelUtil.write2(0, i + 1, 12, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 13, "_", wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				} else if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))) {
					excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 13, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				    }else{
					 excelUtil.write2(0, i + 1, 12, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 13, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 14, retMap.get("AREA"), wb);
				 }
			} /*else if (levels == 3) {// 办事处
				if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))||(retMap.get("JOB")!=null&&retMap.get("JOB").equals("分公司一般查询用户"))) {
					excelUtil.write2(0, i + 1, 10, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 11, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 12, "_", wb);
				}else{
					 excelUtil.write2(0, i + 1, 10, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 11, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
				 }
			}*/ else {// 总部
				if (retMap.get("JOB")!=null&&retMap.get("JOB").equals("分公司管理员")) {
					excelUtil.write2(0, i + 1, 12, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 13, "_", wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				} else if((retMap.get("JOB")!=null&&retMap.get("JOB").equals("办事处管理员"))){
					excelUtil.write2(0, i + 1, 12, retMap.get("AREA"), wb);
					excelUtil.write2(0, i + 1, 13, retMap.get("ORGNAME"), wb);
					excelUtil.write2(0, i + 1, 14, "_", wb);
				    }
				else{
					 excelUtil.write2(0, i + 1, 12, retMap.get("FILIALE"), wb);
					 excelUtil.write2(0, i + 1, 13, retMap.get("AGENCY"), wb);
					 excelUtil.write2(0, i + 1, 14, retMap.get("AREA"), wb);
				 }
			}
		}

		String str = DateUtil.getFullStr(new Date());

		String fileName = "expUser" + str + ".xls";
		try {
			String localHostIp = LocalHostIp.getLocalHostAddress().substring(1);
	//	    String fileIp = dictDao.getByName("FILEIP").getKV().get(1);
			String savePath = "";
			String fileIp=null;
			if (null == fileIp) {
				savePath = retPath + File.separator + fileName;
			} else if (fileIp.equals(localHostIp)) {
				savePath = retPath + File.separator + fileName;
			} else {
				savePath = "\\\\" + fileIp + "/export" + File.separator + fileName;
			}
			System.out.println(savePath);
			excelUtil.saveIn(wb, savePath);
			System.out.println("导出成功————————————>");
			gr.setPint(retList.size());
			gr.setPstr(fileName);
			return gr;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("导出失败————————————>");
			gr.setExp(new Exp("操作失败！"));
			return gr;
		}

	}

	private  String getValue(Object key,String name){
		String sql="SELECT EVALUE FROM TB_DICT WHERE EKEY=? AND NAME=?";
		if(key!=null){
			List<Map<String,Object>> list=jdbcTemplate.queryForList(sql,((BigDecimal) key).intValue(),name);
			Map<String,Object> vMap=list.get(0);
			return vMap.get("EVALUE").toString();
		}
		else return "";
			
	}
	private String getCacsValue(Object key,String name){
		
		String sql;
		if(key!=null&&((BigDecimal) key).intValue()!=0){
			sql="SELECT EVALUE FROM TB_CASC WHERE EKEY=? AND NAME=? ";
			List<Map<String,Object>> list=jdbcTemplate.queryForList(sql,((BigDecimal) key).intValue(),name);
			if(list==null||list.size()==0){
				return "";
			}else{
				Map<String,Object> vMap=list.get(0);
				return vMap.get("EVALUE").toString();
			}
			
		}
		else
			return "";
	}
	// 验证是否是促销员
	public boolean checkIsCxy(String name) {
		String sql = "SELECT TR.NAME AS NAME from (SELECT ROLEID FROM TB_USER_ROLE WHERE USERID IN (SELECT ID FROM TB_USER WHERE NAME=?)) TU " + " LEFT JOIN TB_ROLE TR ON TR.ID=TU.ROLEID ";
		List<String> roleNames = jdbcTemplate.queryForList(sql, String.class, name.trim());
		if (roleNames.contains("促销员") || roleNames.contains("店长")) {
			return true;
		} else {
			return false;

		}

	}

}
